﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using System.IO;
using System.Text.RegularExpressions;
using System.Web.Script.Serialization;
using Json2DataSet;
using NPOI.XSSF.UserModel;
using System.Threading;

namespace WindowsFormsApp2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        string json = "";

        private void button1_Click(object sender, EventArgs e)
        {
            string localFilePath = String.Empty;
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();

            //设置文件类型  
            saveFileDialog1.Filter = " xls files(*.xls)|";
            //设置文件名称：
            saveFileDialog1.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            //设置默认文件类型显示顺序  
            saveFileDialog1.FilterIndex = 2;

            //保存对话框是否记忆上次打开的目录  
            saveFileDialog1.RestoreDirectory = true;

            //点了保存按钮进入  
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                //获得文件路径  
                localFilePath = saveFileDialog1.FileName.ToString();



                //var dataSet = Json2DataSetConverter.Convert(File.ReadAllText("D:\\user_json.txt"));
                ////DataTable dt = JsonToDataTable(File.ReadAllText("D:\\user_json.txt"));
                ////DataTable dt = JsonToDataSet(File.ReadAllText("D:\\user_json.txt")).Tables[0];

                


                ////bool res =GridToExcelByNPOI(dataSet.Tables[0], localFilePath);
                //TableToExcelForXLSX(dataSet.Tables[0], localFilePath);

                //Thread.Sleep(5000);




                //var dataSet2 = Json2DataSetConverter.Convert(File.ReadAllText("D:\\account_json.txt"));

                //TableToExcelForXLSX(dataSet2.Tables[0],@"C:\Users\24947\Desktop\201810200016_account.xlsx");



                var dataSet3 = Json2DataSetConverter.Convert(File.ReadAllText("c:\\json.json"));

                TableToExcelForXLSX(dataSet3.Tables[0], @"C:\json.xlsx");




                //new Comm().DataGridViewToExcel(dataGridViewX1, "", localFilePath);
                MessageBox.Show("导出成功！");
                //if (res)
                //{
                //    MessageBox.Show("导出成功！");
                //}
                //else
                //{
                //    MessageBox.Show("导出失败，请稍后重试！");
                //}
            }

        }



        /// <summary>   
        /// 根据Json返回DateTable,JSON数据格式如:   
        /// {table:[{column1:1,column2:2,column3:3},{column1:1,column2:2,column3:3}]}   
        /// items:{"2750884":{clicknum:"50",title:"鲍鱼",href:"/shop/E06B14B40110/dish/2750884#menu",desc:"<br/>",src:"15f38721-49da-48f0-a283-8057c621b472.jpg",price:78.00,units:"",list:[],joiner:""}}
        /// </summary>   
        /// <param name="strJson">Json字符串</param>   
        /// <returns></returns>   
        public static DataTable JsonToDataTable(string strJson)
        {
            //取出表名   
            //var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
            var rg = new Regex(@"([^:])+(?=:\{)", RegexOptions.IgnoreCase);
            string strName = rg.Match(strJson).Value;
            DataTable tb = null;
            //去除表名   
            //strJson = strJson.Substring(strJson.IndexOf("{") + 1);
            //strJson = strJson.Substring(0, strJson.IndexOf("}"));

            //获取数据   
            //rg = new Regex(@"(?<={)[^}]+(?=})");
            rg = new Regex(@"(?<={)[^}]+(?=})");

            System.Text.RegularExpressions.MatchCollection mc = rg.Matches(strJson);
            for (int i = 0; i < mc.Count; i++)
            {
                string strRow = mc[i].Value;
                string[] strRows = strRow.Split(',');

                //创建表   
                if (tb == null)
                {
                    tb = new DataTable();
                    tb.TableName = strName;
                    foreach (string str in strRows)
                    {
                        var dc = new DataColumn();
                        string[] strCell = str.Split(':');
                        dc.ColumnName = strCell[0];
                        tb.Columns.Add(dc);
                    }
                    tb.AcceptChanges();
                }

                //增加内容   
                DataRow dr = tb.NewRow();
                if (strRow.Length == 5)
                {
                    for (int r = 0; r < strRows.Length; r++)
                    {
                        //dr[r] = strRows[r].Split(':')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");
                        dr[r] = strRows[r];
                    }
                }
                else
                {
                    continue;
                }
                //else if (strRow.Length == 6)
                //{
                //    for (int r = 0; r < strRows.Length; r++)
                //    {
                //        //dr[r] = strRows[r].Split(':')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");
                //        if (strRows[r] != null)
                //        {
                //            dr[r] = strRows[r];
                //        }
                //        else
                //        {
                //            continue;
                //        }
                //    }
                //}

                tb.Rows.Add(dr);
                tb.AcceptChanges();
            }

            return tb;
        }


        /// <summary>  
        ///     将JSON解析成DataSet（只限标准的JSON数据）  
        ///     例如：Json＝{t1:[{name:'数据name',type:'数据type'}]} 或 Json＝{t1:[{name:'数据name',type:'数据type'}],t2:[{id:'数据id',gx:'数据gx',val:'数据val'}]}  
        /// </summary>  
        /// <param name="json">Json字符串</param>  
        /// <returns>DataSet</returns>  
        public static DataSet JsonToDataSet(string json)
        {
            try
            {
                var ds = new DataSet();
                var jss = new JavaScriptSerializer();
                object obj = jss.DeserializeObject(json);
                var datajson = (Dictionary<string, object>)obj;
                foreach (var item in datajson)
                {
                    var dt = new DataTable(item.Key);
                    var rows = (object[])item.Value;
                    foreach (object row in rows)
                    {
                        var val = (Dictionary<string, object>)row;
                        DataRow dr = dt.NewRow();
                        foreach (var sss in val)
                        {
                            if (!dt.Columns.Contains(sss.Key))
                            {
                                dt.Columns.Add(sss.Key);
                                dr[sss.Key] = sss.Value;
                            }
                            else
                                dr[sss.Key] = sss.Value;
                        }
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                }
                return ds;
            }
            catch(Exception ex)
            {
                return null;
            }
        }


        #region Excel2007
        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xlsx)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLSX(string file)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
                ISheet sheet = xssfworkbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }


        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.BLANK: //BLANK:
                    return null;
                case CellType.BOOLEAN: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.NUMERIC: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.STRING: //STRING:
                    return cell.StringCellValue;
                case CellType.ERROR: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.FORMULA: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xlsx)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcelForXLSX(DataTable dt, string file)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet("Test");

            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {

        }
        #endregion
        //public bool GridToExcelByNPOI(DataTable dt, string strExcelFileName)
        //{
        //    try
        //    {
        //        HSSFWorkbook workbook = new HSSFWorkbook();
        //        ISheet sheet = workbook.CreateSheet("Sheet1");

        //        ICellStyle HeadercellStyle = workbook.CreateCellStyle();
        //        //HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

        //        //字体
        //        NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
        //        //headerfont.Boldweight = (short)FontBoldWeight.Bold;
        //        HeadercellStyle.SetFont(headerfont);


        //        //用column name 作为列名
        //        int icolIndex = 0;
        //        IRow headerRow = sheet.CreateRow(0);
        //        foreach (DataColumn item in dt.Columns)
        //        {
        //            ICell cell = headerRow.CreateCell(icolIndex);
        //            cell.SetCellValue(item.ColumnName);
        //            cell.CellStyle = HeadercellStyle;
        //            icolIndex++;
        //        }

        //        ICellStyle cellStyle = workbook.CreateCellStyle();

        //        //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
        //        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
        //        //cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        //        //cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


        //        NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
        //        //cellfont.Boldweight = (short)FontBoldWeight.Normal;
        //        cellStyle.SetFont(cellfont);

        //        //建立内容行
        //        int iRowIndex = 1;
        //        int iCellIndex = 0;
        //        foreach (DataRow Rowitem in dt.Rows)
        //        {
        //            IRow DataRow = sheet.CreateRow(iRowIndex);
        //            foreach (DataColumn Colitem in dt.Columns)
        //            {

        //                ICell cell = DataRow.CreateCell(iCellIndex);
        //                cell.SetCellValue(Rowitem[Colitem].ToString());
        //                cell.CellStyle = cellStyle;
        //                iCellIndex++;
        //            }
        //            iCellIndex = 0;
        //            iRowIndex++;
        //        }

        //        //自适应列宽度
        //        for (int i = 0; i < icolIndex; i++)
        //        {
        //            sheet.AutoSizeColumn(i);
        //        }

        //        //写Excel
        //        FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
        //        workbook.Write(file);
        //        file.Flush();
        //        file.Close();

        //        //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
        //    }
        //    catch (Exception ex)
        //    {
        //        //ILog log = LogManager.GetLogger("Exception Log");
        //        //log.Error(ex.Message + Environment.NewLine + ex.StackTrace);
        //        ////记录AuditTrail
        //        //CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);

        //        //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        return false;
        //    }
        //    finally
        //    {
        //        //    workbook = null; 
        //    }
        //    return true;
        //}

    }
}
